package cn.example.practice.util;

import java.sql.*;
import java.util.Arrays;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * H2数据库工具类，提供数据库连接和操作功能
 */
public class JDBCUtils {
    private static final Logger LOGGER = Logger.getLogger(JDBCUtils.class.getName());

    private static final String DRIVER = "org.h2.Driver";
    private static final String URL = "jdbc:h2:~/shopdb";
    private static final String USERNAME = "sa";
    private static final String PASSWORD = "";

    // 数据库表创建语句
    private static final String CREATE_USER_TABLE =
            "CREATE TABLE IF NOT EXISTS shop_user (" +
                    "    id BIGINT AUTO_INCREMENT PRIMARY KEY," +
                    "    username VARCHAR(50) NOT NULL UNIQUE," +
                    "    password VARCHAR(50) NOT NULL" +
                    ")";

    private static final String CREATE_GOODS_TABLE =
            "CREATE TABLE IF NOT EXISTS goods (" +
                    "    id BIGINT AUTO_INCREMENT PRIMARY KEY," +
                    "    name VARCHAR(100) NOT NULL," +
                    "    price DECIMAL(10,2) NOT NULL," +
                    "    image_data CLOB," +
                    "    image_type VARCHAR(50)" +
                    ")";

    private static final String CREATE_CART_TABLE =
            "CREATE TABLE IF NOT EXISTS shop_cart (" +
                    "    id BIGINT AUTO_INCREMENT PRIMARY KEY," +
                    "    user_id BIGINT NOT NULL," +
                    "    goods_id BIGINT NOT NULL," +
                    "    quantity INT NOT NULL DEFAULT 1," +
                    "    FOREIGN KEY (user_id) REFERENCES shop_user(id)," +
                    "    FOREIGN KEY (goods_id) REFERENCES goods(id)" +
                    ")";

    // 初始化数据
    private static final String[] INITIAL_DATA = {
            "INSERT INTO shop_user (username, password) VALUES ('admin', 'admin123')",
            "INSERT INTO shop_user (username, password) VALUES ('user1', 'password123')",
            "INSERT INTO goods (name, price) VALUES ('可爱洛丽塔套裙', 19.95)",
            "INSERT INTO goods (name, price) VALUES ('夏天40cm娃衣', 25.00)",
            "INSERT INTO goods (name, price) VALUES ('光与夜之恋一番赏娃衣', 18.70)",
            "INSERT INTO goods (name, price) VALUES ('软萌套装棉花衣服', 9.80)",
            "INSERT INTO goods (name, price) VALUES ('恋与深空秦彻娃衣', 18.90)",
            "INSERT INTO goods (name, price) VALUES ('小王子睡衣', 7.80)",
            "INSERT INTO goods (name, price) VALUES ('坐姿海星体玩偶斗篷', 19.90)",
            "INSERT INTO goods (name, price) VALUES ('策策虎冬日狗狗', 32.90)"
    };

    static {
        try {
            // 加载H2驱动
            Class.forName(DRIVER);
            // 初始化数据库
            initializeDatabase();
        } catch (ClassNotFoundException e) {
            LOGGER.log(Level.SEVERE, "Failed to load H2 driver", e);
            throw new RuntimeException("Failed to load H2 driver", e);
        }
    }

    /**
     * 初始化数据库表和测试数据
     */
    private static void initializeDatabase() {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {

            // 创建表
            Arrays.asList(CREATE_USER_TABLE, CREATE_GOODS_TABLE, CREATE_CART_TABLE)
                    .forEach(sql -> executeStatement(stmt, sql));

            // 检查是否已有数据
            ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM shop_user");
            rs.next();
            int userCount = rs.getInt(1);

            // 只有在表为空时才插入初始数据
            if (userCount == 0) {
                Arrays.stream(INITIAL_DATA)
                        .forEach(sql -> executeStatement(stmt, sql));
                LOGGER.info("Database initialized with test data");
            }

        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Failed to initialize database", e);
            throw new RuntimeException("Failed to initialize database", e);
        }
    }

    private static void executeStatement(Statement stmt, String sql) {
        try {
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Error executing statement: " + sql, e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取数据库连接
     */
    public static Connection getConnection() throws SQLException {
        try {
            return DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Failed to get database connection", e);
            throw e;
        }
    }

    /**
     * 关闭数据库资源
     */
    public static void closeResources(AutoCloseable... resources) {
        Arrays.stream(resources)
                .filter(resource -> resource != null)
                .forEach(resource -> {
                    try {
                        resource.close();
                    } catch (Exception e) {
                        LOGGER.log(Level.WARNING, "Error closing resource: " + resource.getClass().getSimpleName(), e);
                    }
                });
    }

    /**
     * 执行更新操作（插入、更新、删除）
     */
    public static int executeUpdate(String sql, Object... params) throws SQLException {
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            setParameters(preparedStatement, params);
            return preparedStatement.executeUpdate();

        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Error executing update: " + sql, e);
            throw e;
        }
    }

    /**
     * 执行查询操作
     */
    public static ResultSet executeQuery(String sql, Object... params) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            setParameters(preparedStatement, params);
            return preparedStatement.executeQuery();

        } catch (SQLException e) {
            closeResources(connection, preparedStatement);
            LOGGER.log(Level.SEVERE, "Error executing query: " + sql, e);
            throw e;
        }
    }

    private static void setParameters(PreparedStatement stmt, Object... params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            stmt.setObject(i + 1, params[i]);
        }
    }
}
